﻿create PROCEDURE Sync.SP_CommitteeBankAccountMainBranchDepositAttachments_ApplyChanges
@RawValues XML 
AS 
BEGIN
	 
		DECLARE @CommitteeBankAccountMainBranchDepositAttachments_Temp TABLE 
		(
			CommitteeBankAccountMainBranchDepositAttachmentID UniqueIdentifier,
			[CommitteeBankAccountMainBranchDepositID] uniqueidentifier,
				[Attachment] uniqueidentifier,
			IsDeleted BIT, 
			LastUpdatedDate DATETIME
		)
		
	   INSERT INTO @CommitteeBankAccountMainBranchDepositAttachments_Temp 
	   SELECT	Tbl.Col.value('CommitteeBankAccountMainBranchDepositAttachmentID[1]','uniqueidentifier') CommitteeBankAccountMainBranchDepositAttachmentID,
                Tbl.Col.value('CommitteeBankAccountMainBranchDepositID[1]', 'uniqueidentifier') [CommitteeBankAccountMainBranchDepositID],
Tbl.Col.value('Attachment[1]', 'uniqueidentifier') [Attachment],
				Tbl.Col.value('IsDeleted[1]', 'bit') IsDeleted,
				Tbl.Col.value('LastUpdatedDate[1]','DateTime') LastUpdatedDate
	   FROM   @RawValues.nodes('/DocumentElement/CommitteeBankAccountMainBranchDepositAttachments') Tbl(Col)
----------------------------------------------------------------------------------------

-- Insert ------------------------------------------------------------------------------

	INSERT INTO [dbo].[CommitteeBankAccountMainBranchDepositAttachments] ([CommitteeBankAccountMainBranchDepositAttachmentID],[CommitteeBankAccountMainBranchDepositID],[Attachment])
	SELECT t.[CommitteeBankAccountMainBranchDepositAttachmentID],t.[CommitteeBankAccountMainBranchDepositID],t.[Attachment]
	FROM  @CommitteeBankAccountMainBranchDepositAttachments_Temp t
	WHERE t.IsDeleted = 0
    AND NOT EXISTS
    (
        SELECT 1 FROM sync.CommitteeBankAccountMainBranchDepositAttachments_Tracking s
        WHERE t.CommitteeBankAccountMainBranchDepositAttachmentID = s.CommitteeBankAccountMainBranchDepositAttachmentID
    )
	-- put conflicts here.
	
-- Update -------------------------------------------------------------------------------
     
	UPDATE [dbo].[CommitteeBankAccountMainBranchDepositAttachments]
	SET [CommitteeBankAccountMainBranchDepositAttachments].[CommitteeBankAccountMainBranchDepositID] = t.[CommitteeBankAccountMainBranchDepositID],[CommitteeBankAccountMainBranchDepositAttachments].[Attachment] = t.[Attachment] 
	FROM [dbo].[CommitteeBankAccountMainBranchDepositAttachments] s JOIN @CommitteeBankAccountMainBranchDepositAttachments_Temp t 
    ON t.CommitteeBankAccountMainBranchDepositAttachmentID = s.CommitteeBankAccountMainBranchDepositAttachmentID
	WHERE	t.IsDeleted = 0 
	AND		t.LastUpdatedDate IS NOT NULL
	AND		t.LastUpdatedDate >= 
				 isnull((SELECT TOP 1 LastUpdatedDate 
				  FROM sync.CommitteeBankAccountMainBranchDepositAttachments_Tracking r
				  WHERE t.CommitteeBankAccountMainBranchDepositAttachmentID = r.CommitteeBankAccountMainBranchDepositAttachmentID)
                  ,t.LastUpdatedDate)
	-----------------------------------------------------------------------------------------

-- Delete -------------------------------------------------------------------------------

	DELETE [dbo].[CommitteeBankAccountMainBranchDepositAttachments]
    FROM @CommitteeBankAccountMainBranchDepositAttachments_Temp t JOIN [dbo].[CommitteeBankAccountMainBranchDepositAttachments] s
    ON t.CommitteeBankAccountMainBranchDepositAttachmentID = s.CommitteeBankAccountMainBranchDepositAttachmentID
	WHERE t.IsDeleted = 1

-----------------------------------------------------------------------------------------
	
END











